How To Add Leading Zeros in Excel [Easy Guide]

您所在的位置：网站首页 › how to print page number in excel 5 easy › How To Add Leading Zeros in Excel [Easy Guide]

How To Add Leading Zeros in Excel [Easy Guide]

2023-08-31 18:54| 来源: 网络整理| 查看: 265

Leading zeros are useful for maintaining consistent data formatting or number length.

For example, if you have a series of ID numbers or codes that are supposed to have a fixed number of digits. Normally if you try adding leading zeros in Excel manually, they will be automatically removed. But there are workarounds.

In this tutorial, we’ll show you exactly how to add leading zeros in Excel.

Table of Contents

In Short: How To Add Leading Zeros in Excel Select the cells you’ll be adding your values in. Right-click on the selected cells. Choose the option “Format cells.” In the field for the category, select “Custom.” In the “Type” field, enter the number of single digits you want for your leading zeros. Click “OK.” Download Our Example Worksheet

You can download our example worksheet to follow along with this guide and help you understand how to have Excel show leading zeros.

Access Worksheet

What Are Leading Zeros?

Adding leading zeros in Excel means prefixing a numeric value or text string with zeros at the beginning. By adding leading zeros, you ensure that a specific number of digits or characters are displayed, even if the original value is shorter.

For example, let’s say you have a set of numbers that represent ID codes, such as 0001, 0002, 0003, 0004, and so on.

If you directly enter these numbers into Excel, it will display them without any leading zeros: 1, 2, 3. However, in some cases, you may want to have Excel add leading zeros to give them a fixed number of digits and display them as the full ID number.

How To Add Leading Zeros in Excel

Now, we’ll show you step-by-step how to can add Excel leading zeros to your data in several ways.

How To Add Leading Zeros by Converting into Text

One way to add leading zeros to Excel is by converting the zeros into text. Unlike using numerical values, Excel usually does not interfere with text, meaning the zeros will not disappear after you input them.

Here’s how to add zeros in front of a number in Excel by converting it into text:

Select the cells you’ll be adding your values in. Add leading zeros in Excel—Select the cells you’ll be adding your values in.

Right-click on the selected cells. Choose the option “Format cells.” Choose the option Format cells.

In the field for the category, select “Text.” In the field for category, select Text.

Click “OK” to confirm.

If you type leading zeros in the cell, the numbers will now appear in Excel with the desired number of leading zeros.

The green sign at the top corner of the cell means that Excel sees that the numeric values are in text format as an error. You can remove it by clicking on the yellow danger sign beside it and choosing the “Ignore Error” option.

This method is perfect for values that you don’t need to do calculations on. However, if you want to work with a formula later, converting to text may be difficult.

Using Custom Number Formatting

For cells that already contain numbers, you may also add custom number formatting. The leading zeros will be added based on the custom format you provide.

If you already have the values in the cells, here’s how to show leading zeros in Excel:

Select the cells with the values.

Right-click on the selected cells. Right click on the selected cells.

Choose the option “Format cells.” Choose the option Format cells.

In the field for the category, select “Custom.” In the field for category, select Custom.

In the “Type” field, enter the number of zeros you want for your leading zeros. In the Type field, enter the number of zero you want for your leading zeros.

In the Type field, enter the number of zero you want for your leading zeros.

The zeroes in the “Type” section represent the number of single digits in your values. You can add more zeros depending on the number of leading zeroes you want to have.

Click “OK.” Leading zeros using custom formatting

Using the CONCATENATE Function

Just like in Google Sheets, the Excel CONCATENATE function is used to join or combine multiple text strings into one continuous string. It allows you to CONCATENATE or merge text from different cells or enter static text along with cell references.

This is the syntax of the CONCATENATE function:

=CONCATENATE(text1, [text2], ...)

With the CONCATENATE function, we can combine the values in our data with the leading zero.

Here’s how to add 0 in front of a number in Excel using the CONCATENATE function:

In the adjacent cell, type the equals sign (=). Type: CONCATENATE( Type the Concatenate function.

In quotes, enter the number of zeros you want for your leading zeros.

Enter the number of zeros you want for your leading zeros first.

Add a comma and select the cell with the value.

Close the brackets and press “Enter.” Formula for adding leading zeros using Concatenate

Copy the formula to the rest of the cells.

Note: No matter how many zeros you add to the CONCATENATE formula, it will only return one zero unless you put them in quotation marks.

Also, remember that this formula will add the same number of zeros to all the cells if copied rather than even out the numbers with different amounts of leading zeros.

Like in the screenshot above, some cells have as little as four digits, while the largest has seven. Yet they all have three leading zeros.

Using the TEXT function

The TEXT function in Excel is used to convert a numeric value into a specified text format. The function takes two arguments: The value you want to convert and the format code that defines the desired text format.

The syntax of the TEXT function is as follows:

=TEXT(value, format_text) value: The numeric value you want to convert to text. format_text: The formatting code determines how the value should be displayed as text.

With the TEXT function, you can add leading zeros in Excel in the same way as we did when converting the values to text.

Here’s add zeros before numbers in Excel using the TEXT function:

In the adjacent cell, type the equals sign (=). Type the TEXT function.

Select the cell with the value first.

Add a comma and enter the number of zeros you want for your leading zeros in quotation marks.

Note: The first zeros will stand in for the number of single digits you’ll have in your values. Therefore, four zeros stand for four digits. Zeros will be added to your values until they get to four digits.

Close the brackets and click “Enter.” Leading zeros added

Copy the formula to the rest of the cells. Formula for adding leading zeros using TEXT function

From the example above, the TEXT function evens out the number of leading zeros in each value. This means that all the values have the same number of single digits.

One thing to remember is that this function converts your values to text. Therefore, you won’t be able to use them in calculations.

Using the RIGHT Function

If your values have text in them or letters, then it may become a bit more difficult to add leading zeros in Excel. One function you can use is the RIGHT function. This function lets you retrieve a portion of text starting from the rightmost characters based on the number of characters you specify.

If you have fewer characters than the number you’ve specified, the function will add zeros to fill in the characters.

Here’s how to add a zero in front of a number in Excel using the CONCATENATE function:

In the adjacent cell, type the equals sign (=). Type the RIGHT function.

After the brackets, enter the maximum number of 0s in quotation marks. After the brackets enter the maximum number of 0s in quotation marks

Add the “and” sign (&) and select the cell with the value. Add the & sign and select the cell with the value then close the brackets.

Add a comma and enter the number of characters you want.

Close the brackets and click “Enter.” Copy the formula to the rest of the cells. Adding leading zeros using the RIGHT function.

Using the REPT Function to Add Dynamic Leading Zeros

The REPT function in Excel is used to repeat a text string a specified number of times. It allows you to create a repeated string or a pattern by specifying the text and the number of repetitions.

The syntax of the REPT function is as follows:

=REPT(text, number_of_times) text: This represents the text string you want to repeat. number_of_times: This refers to the number of times you want to repeat the text string.

Unlike the TEXT function, which only works with numbers, the REPT function works with values that have both numbers and text in them.

We’ll combine the REPT function with the LEN function to decide the number of leading zeros we add to our values.

Here’s how to add 0 in front of a number in Excel using the CONCATENATE function:

In the adjacent cell, type the equals sign (=). Type the REPT function.

After the brackets, enter a 0 and add a comma. After the brackets enter a 0 and add a comma

Enter the number of single digits you want for your leading zeros, then add the LEN function. Enter the number of digits you want for your leading zeros then add the LEN function.

Enter the number of digits you want for your leading zeros then add the LEN function.

In the cell brackets, select the cell with the value, then close the brackets. In the cell brackets select the cell with the value then close the brackets.

Add the “and” (&) symbol and select the cell value again. Add the & symbol and select the cell value again.

Close the brackets and click “Enter.” Copy the formula to the rest of the cells. Formula for adding leading zeros in Excel using REPT function

This formula also works the same if you have letters in your values, like in the example below:

Adding leading zeros to alphanumeric values

The formula converts the numeric values to text, meaning they can’t be used in calculations with certain formulas.

How To Remove Leading Zeros in Excel

Now that you know how to insert leading zeros in Excel, we’ll also show you how to remove them. Depending on how you added the leading zeros in Excel, there are a number of ways to remove them.

Removing Leading Zeros by Converting the Text Format

The first method would be to convert the values from Text back to Numbers. Here’s how:

Select the cells with the data. Click on the yellow error sign.

Choose the option “Convert to Number.” Choose the option Convert to Number

Your data will revert back to being without reading zeros.

Data reverted back to being without reading zeros.

This method only works if you still have the error sign in your data. If you attempt to convert the data to numbers in any other way, the leading zeros will still remain.

Removing Leading Zeros by Changing the Custom Formatting

If you had added your leading zeros using Custom formatting, then here’s how to remove them:

Select the cells with the data.

In the “Home” tab, go to “Special” from the drop-down options. In the Home tab, the Special option

Choose the “General” option. Choose the option General.

All your leading zeros will then disappear, and your values will revert to normal numeric values.

Removing Leading Zeros Using Functions

When removing leading zeros in Excel, the best function to use is the VALUE function. The VALUE function in Excel is used to convert a text string that has numerical values into an actual numeric value. It also helps to convert text values that Excel interprets as text rather than numbers into numerical values that can be used for calculations or other operations.

Here’s how:

Click in the cell and type the equals sign (=). Enter the VALUE function.

Select the cell with the values with leading zeros.

Click “Enter.” Copy the formula to the rest of the cells. Data reverted back to being without reading zeros.

The formula converts the values to numbers and removes the leading zeros in the process.

Bottom Line

Inserting leading zeros in Excel is a useful method for maintaining constant formatting, sorting, and data visualization.

In this guide, we’ve shown you how to add leading zeros in Excel and how to remove them. You may quickly add leading zeros to integers or text values by adopting a variety of techniques, including formatting cells, using functions like TEXT or CONCATENATE, or using the find and replace functionality.

Other than this tutorial, we have a detailed guide for the best Excel courses to help you master Excel easily and quickly.

Alternatively, you may want to check out our premium template library to access some awesome ready-made spreadsheets. Remember, you can use the code SSP to save 50% at checkout.

Get Premium Templates

How To Insert Bullet Points in Excel [Easy Guide] How To Solve the #SPILL Error in Excel [Easy Guide] How To Find Duplicates in Excel [Easy Guide] How To Count Unique Values in Excel [Guide] Can Excel Count Cells with Text? [Easy Guide] 4 Easy Examples of Greater Than or Equal To in Excel How To Use Strikethrough in Excel (5 Easy Ways)

【本文地址】

How To Add Leading Zeros in Excel [Easy Guide]

How To Add Leading Zeros in Excel [Easy Guide]

今日新闻

推荐新闻